from wordcloud import WordCloud
import json
import pymysql
import pygal
import jieba
import collections


class TaoBao:

    def __init__(self):
        pass

    def parse_file(self):
        with open('tb_comments_1.json', encoding='utf-8') as f:
            comments = json.load(f)
            comments = comments["rateDetail"]["rateList"]
            # print(comments)
        return comments

    def comments_list(self):
        comments = self.parse_file()
        content_list = []
        for i in comments:
            item = {}
            item["content"] = i["rateContent"]
            item["rateDate"] = i["rateDate"]
            item["comment_id"] = i["id"]
            item["auctionSku"] = i["auctionSku"]
            # print(i["appendComment"])
            # print(i["rateContent"])
            if i["appendComment"] == None:
                item["append_content"] = '用户没有追加评论'
            else:
                item["append_content"] = i["appendComment"]["content"]
                print(item["append_content"])
            content_list.append(item)
        print(content_list)
        return content_list

    def get_mysql(self):
        """
            连接数据库
            :return:
            """
        try:
            connection = pymysql.connect(
                host="localhost",
                port=3306,
                user="root",
                password="123",
                db="TaoBao",
                charset='utf8mb4',  # 可以省略,8.0客户端默认utf-8可以省略，5.x最好带上
                cursorclass=pymysql.cursors.DictCursor  # 返回字典格式的结果集。不写默认返回元组格式

            )
        except Exception as e:
            print(e, '数据库连接失败，检查用户名、密码、数据库名')
        try:
            cursor = connection.cursor()
        except Exception as e:
            print(e, '获取游标失败，上个游标已存在未关闭？')
        return connection, cursor

    def create_table(self):
        cursor = self.get_mysql()
        cursor.execute("""CREATE TABLE comments(
                      id INT PRIMARY KEY AUTO_INCREMENT,
                       comment_id BIGINT,
                       rate_content text,
                       rate_date datetime,
                       append_content text,
                       action_sku text);""")
        return

    def insert_sql(self):
        connection, cursor = self.get_mysql()
        content_list = self.comments_list()
        for comment in content_list:
            comment_id = comment["comment_id"]
            rate_content = comment["content"]
            rateDate = comment["rateDate"]
            auctionSku = comment["auctionSku"]
            append_content = comment["append_content"]

            print(comment_id,rate_content,rateDate,append_content,auctionSku,)
            i_sql = """insert into comments(comment_id,rate_content, rate_date, append_content, action_sku)VALUES(%s,%s,%s,%s,%s)"""
            data = (comment_id, rate_content, rateDate, append_content, auctionSku)
            cursor.execute(i_sql, data)
            connection.commit()

    def rate_data_30(self):
        connection, cursor = self.get_mysql()
        """读数据库根据rate_date倒序前30条评论的 评论"""
        sql = """SELECT rate_content FROM comments  """
        cursor.execute(sql)
        rs_set = cursor.fetchall()
        print(rs_set)
        text = ''
        for line in rs_set:
            content = line['rate_content']
            line = content.strip('\n')
            text += ''.join(jieba.cut(line))
        print(text)
        return text

    def segment(self):
        text = self.rate_data_30()
        results = jieba.cut(text)
        word_list = []
        for i in results:
            word_list.append(i)
        # 词频统计
        word_counts = collections.Counter(word_list)  # 对分词做词频统计
        word_counts_top = word_counts.most_common(20)  # 获取前20最高频的词
        # print(word_counts)    # 输出检查
        # print(word_counts_top)  # 输出检查
        return word_counts

    def draw_WordCloud(self):
        word_counts = self.segment()
        font = "C:\\Windows\\Fonts\\msyh.ttc"
        wc = WordCloud(font_path=font,  # 如果是中文必须要添加这个，否则会显示成方框
                       background_color='white',  # 背景色
                       width=1000,
                       height=800,
                       max_words=2000,  # 最多显示词数
                       max_font_size=300,
                       min_font_size=50,
                       ).generate_from_frequencies(word_counts)
        # print(str(word_counts_top))
        wc.to_file('考试.png')  # 保存图片

    def select_action_sku(self):
        s_sql = """SElECT count(id) as amount, action_sku  FROM comments GROUP BY action_sku"""
        connection, cursor = self.get_mysql()
        cursor.execute(s_sql)
        rs_set = cursor.fetchall()
        if not rs_set:
            print('sql:{}  查询结果集空，无法生成饼状图'.format(s_sql))

        # 生成图表
        pie_chart = pygal.Pie()
        pie_chart.title = '手机占比图'

        amount = 0  # 购买总人数吗，用于计算比例
        for rs in rs_set:
            amount += rs["amount"]

        for rs in rs_set:
            # pie_chart.add('Firefox', 36.6)
            pie_chart.add(rs["action_sku"], round(rs["amount"] / amount * 100, 1))
        pie_chart.render_to_file('4.svg')
        print('已生成图表，路径{}'.format('4京东评论.svg'))
        return


if __name__ == "__main__":
    tb = TaoBao()
    tb.select_action_sku()
    tb.draw_WordCloud()